"""
Case Type   : 行存压缩
Case Name   : 分区表非压缩表，修改为压缩表，使用gs_basebackup备份
Create At   : 2023/8/10
Owner       : li-xin12345
Description :
    1、建分区表（非压缩表），并插入数据
    2、修改分区表为压缩表
    3、查看表占用磁盘大小、数据量等
    4、新建目录
    5、执行备份
    6、停止数据库
    7、使用备份目录启动数据库
    8、查询数据库状态确认是否启动成功
    9、查询测试表是否存在，查看表占用磁盘大小、数据量等，与备份前对比
    10、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、成功
    6、成功
    7、成功
    8、成功
    9、与备份前一致
    10、成功
History     :
"""

import os
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

Pri_U_SH = CommonSH('PrimaryDbUser')
Node_Num = Pri_U_SH.get_node_num()


@unittest.skipIf(3 > Node_Num, '不满足一主两备环境跳过')
class RowCompressTest(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.std1_sh = CommonSH('Standby1DbUser')
        self.std2_sh = CommonSH('Standby2DbUser')
        self.pri_user = Node('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.tb_name = 't_alter_rowcompress_0063'
        self.backup_dir = 'f_aler_rowcompress_0063'
        self.backup_path = os.path.join(os.path.dirname(
            macro.DB_INSTANCE_PATH), self.backup_dir)
        self.alter_compress_param = 'compresstype=2, ' \
                                    'compress_chunk_size=4096, ' \
                                    'compress_prealloc_chunks=1, ' \
                                    'compress_byte_convert=true, ' \
                                    'compress_diff_convert=true, ' \
                                    'compress_level=14'

    def test_row_compress(self):
        text = '-----step1:建分区表（非压缩表），并插入数据;expect:成功-----'
        self.log.info(text)
        sql = f'''drop table if exists {self.tb_name} cascade;
                create table {self.tb_name}
                (prod_id number(6),
                 cust_id number,
                 time_id date,
                 channel_id char(1),
                 promo_id number(6),
                 quantity_sold number(3),
                 amount_sold number(10,2)
                )
                partition by range (time_id)
                interval('1 day')
                ( partition p1 values less than ('2023-02-01 00:00:00'),
                  partition p2 values less than ('2023-02-02 00:00:00')
                );

                insert into {self.tb_name} values(generate_series(0, 1000), 
                lpad(floor(random() * 1000)::text, 2, '0'), 
                '2023-01-10 00:00:00', 'a', 1, 1, 1);
                insert into {self.tb_name} values(generate_series(0, 1000), 
                lpad(floor(random() * 1000)::text, 2, '0'), 
                '2023-02-01 00:00:00', 'a', 2, 2, 2);
                '''
        sql_res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, sql_res,
                      '执行失败：' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_res,
                      '执行失败：' + text)
        self.assertIn('INSERT 0 1001', sql_res, '执行失败：' + text)
        self.assertEqual(sql_res.count('INSERT 0 1001'), 2, '执行失败：' + text)

        text = '-----step2:修改分区表为压缩表;expect:成功-----'
        self.log.info(text)
        sql = f'alter table {self.tb_name} set ' \
              f'({self.alter_compress_param}); checkpoint;' \
              f'\\d+ {self.tb_name}'
        sql_res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_res,
                      '执行失败：' + text)
        self.assertIn(self.constant.CHECKPOINT_SUCCESS_MSG, sql_res,
                      '执行失败：' + text)
        self.assertIn(f'{self.alter_compress_param}', sql_res,
                      '执行失败：' + text)

        text = '-----step3:查看表占用磁盘大小、数据量等-----'
        self.log.info(text)
        sql = f"select pg_size_pretty(pg_total_relation_size(" \
              f"'{self.tb_name}')) as table_size;"
        res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(res)
        self.assertNotIn('ERROR', res, '执行失败：' + text)

        sql = f'select checksum({self.tb_name}::text) from {self.tb_name};'
        before_size = Pri_U_SH.execut_db_sql(sql)
        self.log.info(before_size)
        self.assertNotIn('ERROR', before_size, '执行失败：' + text)

        text = '-----step4:新建目录-----'
        self.log.info(text)
        cmd = f"rm -rf {self.backup_path};mkdir -p {self.backup_path};" \
              f"chmod 700 {self.backup_path};" \
              f"ls {os.path.dirname(macro.DB_INSTANCE_PATH)} | " \
              f"grep '{self.backup_dir}';"
        self.log.info(cmd)
        res = self.common.get_sh_result(self.pri_user, cmd)
        self.assertIn(f'{self.backup_dir}', res, '执行失败：' + text)

        self.log.info('-----step5:执行备份;expect:成功-----')
        cmd = f"source {macro.DB_ENV_PATH};" \
              f"gs_basebackup -D {self.backup_path} -Fp -X stream " \
              f"-p {self.pri_user.db_port} -l gauss_042.bak " \
              f"-U {self.pri_user.ssh_user} -w"
        self.log.info(cmd)
        res = self.common.get_sh_result(self.pri_user, cmd)
        self.assertIn('base backup successfully', res, '执行失败：' + text)

        self.log.info('-----step6:停止数据库;expect:成功-----')
        stop_res = Pri_U_SH.stop_db_instance()
        self.assertIn(self.constant.GS_CTL_STOP_SUCCESS_MSG, stop_res,
                      '执行失败：' + text)

        self.log.info('-----step7:使用备份目录启动数据库;expect:成功-----')
        start_res = Pri_U_SH.start_db_instance(dn_path=self.backup_path)
        self.assertIn(self.constant.RESTART_SUCCESS_MSG, start_res,
                      '执行失败：' + text)

        self.log.info('-----step8:查询数据库状态确认是否启动成功;expect:成功-----')
        res1 = self.std1_sh.build_standby(mode='')
        self.assertIn(self.constant.BUILD_SUCCESS_MSG, res1, '备1build失败')
        res2 = self.std2_sh.build_standby(mode='')
        self.assertIn(self.constant.BUILD_SUCCESS_MSG, res2, '备2build失败')

        cmd = f"source {macro.DB_ENV_PATH};" \
              f"gs_ctl query -D {self.backup_path}"
        self.log.info(cmd)
        status = self.common.get_sh_result(self.pri_user, cmd)
        self.assertTrue('Normal' or 'Degraded' in status, '数据库状态异常')

        text = '-----step9:再次查看表占用磁盘大小、数据量等，与备份前对比;' \
               'expect:查看数据与备份前一致-----'
        self.log.info(text)
        sql = f"select pg_size_pretty(pg_total_relation_size(" \
              f"'{self.tb_name}')) as table_size;"
        res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(res)
        self.assertNotIn('ERROR', res, '执行失败：' + text)

        sql = f'select checksum({self.tb_name}::text) from {self.tb_name};'
        after_size = Pri_U_SH.execut_db_sql(sql)
        self.log.info(after_size)
        self.assertNotIn('ERROR', before_size, '执行失败：' + text)
        self.assertEqual(before_size, after_size, '执行失败：' + text)

    def tearDown(self):
        text = '-----step10:清理环境; expect:成功-----'
        self.log.info(text)
        stop_res = Pri_U_SH.stop_db_instance(dn_path=self.backup_path)
        self.log.info(stop_res)
        start_res = Pri_U_SH.start_db_instance()
        self.log.info(start_res)

        cmd = f'rm -rf {self.backup_path};' \
              f'if [ -d {self.backup_path} ]; ' \
              f'then echo "exists"; else echo "not exists"; fi'
        rm_res = self.common.get_sh_result(self.pri_user, cmd)
        self.log.info(rm_res)

        sql = f'drop table if exists {self.tb_name};'
        drop_res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(drop_res)

        status = Pri_U_SH.get_db_cluster_status(param='status')
        self.log.info(status)
        if status is False:
            build_res1 = self.std1_sh.build_standby(mode='')
            self.assertIn(self.constant.BUILD_SUCCESS_MSG, build_res1)
            build_res2 = self.std2_sh.build_standby(mode='')
            self.assertIn(self.constant.BUILD_SUCCESS_MSG, build_res2)
            status = Pri_U_SH.get_db_cluster_status(param='status')
            self.log.info(status)

        self.assertIn(self.constant.GS_CTL_STOP_SUCCESS_MSG, stop_res,
                      '执行失败：' + text)
        self.assertIn(self.constant.RESTART_SUCCESS_MSG, start_res,
                      '执行失败：' + text)
        self.assertEqual('not exists', rm_res, '文件删除失败')
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_res,
                      '执行失败：' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
